# -*- coding:utf-8 -*-
import xlrd
import xlwt
import os

class ExcelWrite(object):
    '''
    excel写入

    :Attributes:
     - encoding: 写入excel的编码格式，默认是unicode
    '''

    def __init__(self, encoding='unicode_escape'):
        self.workbook = xlwt.Workbook(encoding = encoding)

    def save(self, file):
        '''
        保存到文件

        :Args:
         - file: 文件路径

        :Example:
         - 例: excel.save('D:/a.xls')
        '''
        # 先删除已存在文件
        if os.path.exists(file):
            os.remove(file)
        # 保存文件
        self.workbook.save(file)

    def write_by_table_data(self, tabledata, rows, cols, sheetname='sheet1'):
        '''
        将table数据列表写入xls文件

        :Args:
         - sheetname:Excel的sheet页名称
         - tabledata: 表格数据列表
         - rows: 数据行数
         - cols: 数据列数

        :Example:
         - 例: excel.write_by_table_data(['name','age','admin','24'],2,2)
        '''
        sheet = self.workbook.add_sheet(sheetname)
        for row in range(0, int(rows)):
            for col in range(0, int(cols)):
                sheet.write(row, col, tabledata[int(col + (row * cols))])

    def write_data_by_row(self, tabledata, sheetname='sheet1'):
        '''
        将数据按行写入到excel

        :Args:
         - tabledata: 数据列表，一般为二维数组
         - sheetname: excel的sheet页名称

        :Example:
         - 例: excel.write_data_by_row([['name','age'],['admin','24']])
        '''
        sheet = self.workbook.add_sheet(sheetname)
        for row in range(len(tabledata)):
            for col in range(len(tabledata[row])):
                sheet.write(row, col, tabledata[row][col])

    def write_data_by_col(self, tabledata, sheetname='sheet1'):
        '''
        将数据按列写入到excel

        :Args:
         - tabledata: 数据列表，一般为二维数组
         - sheetname: excel的sheet页名称

        :Example:
         - 例: excel.write_data_by_col([['name','age'],['admin','24']])
        '''
        sheet = self.workbook.add_sheet(sheetname)
        for col in range(len(tabledata)):
            for row in range(len(tabledata[col])):
                sheet.write(row, col, tabledata[col][row])

class ExcelRead(object):
    '''
    excel读取

    :Attributes:
     - file: 待读取的excel表路径
    '''

    def __init__(self, file):
        self.workbook = xlrd.open_workbook(file)

    def read_element_from_xls(self):
        '''
        获取xls表第一个sheet页的数据，A列为键，B列为值

        :Returns:
            返回excel表第一个sheet页的数据，字典类型

        :Example:
         - 例: ExcelRead('D:/a.xls').read_element_from_xls()
        '''
        xls_values = dict()
        # 通过索引顺序获取工作表
        table = self.workbook.sheet_by_index(0)
        for i in range(table.nrows):
            xls_values[table.cell(i, 0).value] = table.cell(i, 1).value
        return xls_values

if __name__ == '__main__':
    ee = ExcelRead('自营持仓信息场外导入.xlsx')
    print(ee.read_element_from_xls())